import { Router } from 'express';
import { body } from 'express-validator';
import validateFormData from '../helpers/validate_form.js';
import * as XLSX from 'xlsx';
import DB from '../models/db.js';


const router = Router();




/**
 * Route to list courses records
 * @GET /courses/index/{fieldname}/{fieldvalue}
 */
router.get(['/', '/index/:fieldname?/:fieldvalue?'], async (req, res) => {  
	try{
		const query = {};
		let queryFilters = [];
		let where = {};
		let replacements = {};
		let fieldName = req.params.fieldname;
		let fieldValue = req.params.fieldvalue;
		
		if (fieldName){
			queryFilters.push(DB.filterBy(fieldName, fieldValue));
		}
		let search = req.query.search;
		if(search){
			let searchFields = DB.Courses.searchFields();
			where[DB.op.or] = searchFields;
			replacements.search = `%${search}%`;
		}
		
		if(queryFilters.length){
			where[DB.op.and] = queryFilters;
		}
		query.raw = true;
		query.where = where;
		query.replacements = replacements;
		query.order = DB.getOrderBy(req, 'course_id', 'desc');
		query.attributes = DB.Courses.listFields();
		let page = parseInt(req.query.page) || 1;
		let limit = parseInt(req.query.limit) || 10;
		let result = await DB.Courses.paginate(query, page, limit);
		return res.ok(result);
	}
	catch(err) {
		return res.serverError(err);
	}
});


/**
 * Route to view Courses record
 * @GET /courses/view/{recid}
 */
router.get('/view/:recid', async (req, res) => {
	try{
		const recid = req.params.recid || null;
		const query = {}
		const where = {}
		where['course_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Courses.viewFields();
		let record = await DB.Courses.findOne(query);
		if(!record){
			return res.notFound();
		}
		return res.ok(record);
	}
	catch(err){
		return res.serverError(err);
	}
});


/**
 * Route to insert Courses record
 * @POST /courses/add
 */
router.post('/add/', 
	[
		body('course_name').not().isEmpty(),
		body('description').optional({nullable: true, checkFalsy: true}),
		body('credit_hours').optional({nullable: true, checkFalsy: true}).isNumeric(),
		body('daily_weight').not().isEmpty().isNumeric(),
		body('exam_weight').not().isEmpty().isNumeric(),
		body('homework_weight').not().isEmpty().isNumeric(),
		body('start_date').optional({nullable: true, checkFalsy: true}),
		body('end_date').optional({nullable: true, checkFalsy: true}),
		body('teacher_id').not().isEmpty(),
		body('status').optional({nullable: true, checkFalsy: true}),
		body('course_number').not().isEmpty(),
	], validateFormData
, async function (req, res) {
	try{
		let modeldata = req.getValidFormData();
		
		// 打印接收到的数据
		console.log('Received course data:', modeldata);
		
		// 确保数值字段为数字类型
		modeldata.credit_hours = parseFloat(modeldata.credit_hours) || 0;
		modeldata.daily_weight = parseFloat(modeldata.daily_weight) || 0;
		modeldata.exam_weight = parseFloat(modeldata.exam_weight) || 0;
		modeldata.homework_weight = parseFloat(modeldata.homework_weight) || 0;
		
		// 格式化日期
		if (modeldata.start_date) {
			modeldata.start_date = new Date(modeldata.start_date).toISOString().split('T')[0];
		}
		if (modeldata.end_date) {
			modeldata.end_date = new Date(modeldata.end_date).toISOString().split('T')[0];
		}
		
		// 添加时间戳
		const now = new Date().toISOString();
		modeldata.created_at = now;
		modeldata.updated_at = now;
		
		// 打印处理后的数据
		console.log('Processed course data:', modeldata);
		
		//save Courses record
		let record = await DB.Courses.create(modeldata);
		
		return res.ok(record);
	} catch(err){
		// 打印详细错误信息
		console.error('Error creating course:', {
			message: err.message,
			stack: err.stack,
			details: err.original ? {
				code: err.original.code,
				errno: err.original.errno,
				sqlMessage: err.original.sqlMessage,
				sqlState: err.original.sqlState
			} : null
		});
		
		// 返回更详细的错误信息
		return res.serverError({
			error: err.message,
			details: err.original ? err.original.sqlMessage : 'Database error occurred'
		});
	}
});


/**
 * Route to get  Courses record for edit
 * @GET /courses/edit/{recid}
 */
router.get('/edit/:recid', async (req, res) => {
	try{
		const recid = req.params.recid;
		const query = {};
		const where = {};
		where['course_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Courses.editFields();
		let record = await DB.Courses.findOne(query);
		if(!record){
			return res.notFound();
		}
		return res.ok(record);
	}
	catch(err){
		return res.serverError(err);
	}
});


/**
 * Route to update  Courses record
 * @POST /courses/edit/{recid}
 */
router.post('/edit/:recid', 
	[
		body('course_id').optional({nullable: true}).not().isEmpty().isNumeric(),
		body('course_name').not().isEmpty(),
		body('description').optional({nullable: true, checkFalsy: true}),
		body('credit_hours').optional({nullable: true, checkFalsy: true}).isNumeric(),
		body('daily_weight').not().isEmpty().isNumeric(),
		body('exam_weight').not().isEmpty().isNumeric(),
		body('homework_weight').not().isEmpty().isNumeric(),
		body('start_date').optional({nullable: true, checkFalsy: true}),
		body('end_date').optional({nullable: true, checkFalsy: true}),
		body('teacher_id').not().isEmpty(),
		body('status').optional({nullable: true, checkFalsy: true}),
		body('course_number').not().isEmpty(),
	], validateFormData
, async (req, res) => {
	try{
		const recid = req.params.recid;
		let modeldata = req.getValidFormData({ includeOptionals: true });
		
		// 打印接收到的数据
		console.log('Received course update data:', modeldata);
		
		// 确保数值字段为数字类型
		modeldata.credit_hours = parseFloat(modeldata.credit_hours) || 0;
		modeldata.daily_weight = parseFloat(modeldata.daily_weight) || 0;
		modeldata.exam_weight = parseFloat(modeldata.exam_weight) || 0;
		modeldata.homework_weight = parseFloat(modeldata.homework_weight) || 0;
		
		// 格式化日期
		if (modeldata.start_date) {
			modeldata.start_date = new Date(modeldata.start_date).toISOString().split('T')[0];
		}
		if (modeldata.end_date) {
			modeldata.end_date = new Date(modeldata.end_date).toISOString().split('T')[0];
		}
		
		// 更新时间戳
		modeldata.updated_at = new Date().toISOString();
		
		// 打印处理后的数据
		console.log('Processed course update data:', modeldata);
		
		const query = {};
		const where = {};
		where['course_id'] = recid;
		query.raw = true;
		query.where = where;
		
		// 检查记录是否存在
		let record = await DB.Courses.findOne(query);
		if(!record){
			return res.notFound();
		}
		
		// 更新记录
		await DB.Courses.update(modeldata, {where: where});
		
		// 返回更新后的数据
		return res.ok(modeldata);
	}
	catch(err){
		// 打印详细错误信息
		console.error('Error updating course:', {
			message: err.message,
			stack: err.stack,
			details: err.original ? {
				code: err.original.code,
				errno: err.original.errno,
				sqlMessage: err.original.sqlMessage,
				sqlState: err.original.sqlState
			} : null
		});
		
		// 返回更详细的错误信息
		return res.serverError({
			error: err.message,
			details: err.original ? err.original.sqlMessage : 'Database error occurred'
		});
	}
});


/**
 * Route to delete Courses record by table primary key
 * Multi delete supported by recid separated by comma(,)
 * @GET /courses/delete/{recid}
 */
router.get('/delete/:recid', async (req, res) => {
	try{
		const recid = (req.params.recid || '').split(',');
		const query = {};
		const where = {};
		where['course_id'] = recid;
		query.raw = true;
		query.where = where;
		let records = await DB.Courses.findAll(query);
		records.forEach(async (record) => { 
			//perform action on each record before delete
		});
		await DB.Courses.destroy(query);
		return res.ok(recid);
	}
	catch(err){
		return res.serverError(err);
	}
});

/**
 * Route to download courses template
 * @GET /courses/template
 */
router.get('/template', async (req, res) => {
    try {
        const workbook = XLSX.utils.book_new();
        
        // Define headers in both Chinese and English
        const headers = [
            ['课程名称', '课程描述', '学分', '平时成绩权重', '考试成绩权重', '作业成绩权重', '开始日期', '结束日期', '教师ID', '状态', '课程编号'],
            ['course_name', 'description', 'credit_hours', 'daily_weight', 'exam_weight', 'homework_weight', 'start_date', 'end_date', 'teacher_id', 'status', 'course_number']
        ];

        // Create worksheet with both header rows
        const worksheet = XLSX.utils.aoa_to_sheet(headers);
        
        // Set column widths
        const colWidths = [
            { wch: 20 }, // course_name
            { wch: 30 }, // description
            { wch: 10 }, // credit_hours
            { wch: 15 }, // daily_weight
            { wch: 15 }, // exam_weight
            { wch: 15 }, // homework_weight
            { wch: 15 }, // start_date
            { wch: 15 }, // end_date
            { wch: 10 }, // teacher_id
            { wch: 10 }, // status
            { wch: 15 }  // course_number
        ];
        worksheet['!cols'] = colWidths;

        XLSX.utils.book_append_sheet(workbook, worksheet, 'Courses');

        // Generate buffer
        const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

        // Set response headers
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', 'attachment; filename=courses_template.xlsx');

        // Send file
        res.send(buffer);
    } catch (err) {
        return res.serverError(err);
    }
});

/**
 * Route to bulk import courses
 * @POST /courses/bulk-import
 */
router.post('/bulk-import', async (req, res) => {
    try {
        const courses = req.body;
        const now = new Date().toISOString();

        // Process each course
        for (const course of courses) {
            // Validate required fields
            if (!course.course_name || !course.course_number) {
                continue;
            }

            // Prepare course data
            const courseData = {
                course_name: course.course_name,
                description: course.description || null,
                credit_hours: parseFloat(course.credit_hours) || 0,
                daily_weight: parseFloat(course.daily_weight) || 0,
                exam_weight: parseFloat(course.exam_weight) || 0,
                homework_weight: parseFloat(course.homework_weight) || 0,
                start_date: course.start_date || null,
                end_date: course.end_date || null,
                teacher_id: course.teacher_id || null,
                status: course.status || 'active',
                course_number: course.course_number,
                created_at: now,
                updated_at: now
            };

            // Update or create course
            await DB.Courses.upsert(courseData, {
                where: { course_number: course.course_number }
            });
        }

        return res.ok({ message: 'Courses imported successfully' });
    } catch (err) {
        return res.serverError(err);
    }
});

export default router;
